import time

from sqlalchemy.dialects import postgresql
from sqlalchemy.orm import Session

from . import schema, model


def get_user(db: Session, user_id: int):
    return db.query(model.User).filter(model.User.userid == user_id).first()


def get_user_by_username(db: Session, username: str):
    return db.query(model.User).filter(model.User.username == username).first()


def user_check_password(db: Session, username: str, password: str):
    return db.query(model.User).filter(model.User.username == username and model.User.password == password).first()


def get_user_by_email(db: Session, email: str):
    return db.query(model.User).filter(model.User.email == email).first()


def get_users(db: Session, skip: int = 0, limit: int = 100):
    return db.query(model.User).offset(skip).limit(limit).all()


def create_user(db: Session, inuser: schema.UserCreate):
    db_user = model.User(username=inuser.username, email=inuser.email, password=inuser.password)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user


def get_post_by_reviewed(db: Session, reviewed: int):
    results = db.query(model.Post).join(model.Post.user).filter(model.Post.reviewed == reviewed).all()
    resultlist = []
    for result in results:
        post = {'post_id': result.post_id,
                'post_title': result.post_title,
                'post_content': result.post_content,
                'post_file': result.post_file,
                'author': result.user.username
                }
        resultlist.append(post)
    return resultlist


def add_post(db, post):
    db_post = model.Post(post_title=post.post_title, post_content=post.post_content, post_file=post.post_file)
    db.add(db_post)
    db.flush()
    db_user = get_user_by_username(db=db, username=post.author)
    db.add(db_user)
    db.flush()
    db_post_user = postgresql.insert(model.post_user).values(postid=db_post.post_id, userid=db_user.userid)
    db.execute(db_post_user)
    db.commit()
    db.refresh(db_post)
    return db_post


def get_posts_by_username(db: Session, username: str):
    return (db.query(model.Post)
            .join(model.post_user)
            .join(model.User)
            .group_by(model.Post.post_id)
            .filter(model.User.username == username)
            .all())


def update_post(db: Session, post: schema.PostBase, postid: int):
    (db.query(model.Post)
     .filter(model.Post.post_id == postid)
     .update({model.Post.post_file: post.post_file,
              model.Post.post_title: post.post_title,
              model.Post.post_content: post.post_content}))
    db.commit()


def delete_post(db: Session, postid: int):
    post = db.query(model.Post).filter(model.Post.post_id == postid).first()
    db.delete(post)
    return db.commit()


def review_post(db, post_id, choice):
    post = db.query(model.Post).filter(model.Post.post_id == post_id).first()
    post.reviewed = choice
    return db.commit()


def add_banner(db, banner: schema.Banner):
    db_banner = model.Banner(content=banner.content, expire_date=banner.date)
    db.add(db_banner)
    db.commit()
    db.refresh(db_banner)
    return db_banner


def get_banners(db):
    return db.query(model.Banner).all()
